package com.yfuxdeer.youchat.dao.impl;


import com.yfuxdeer.youchat.dao.MessageDao;
import com.yfuxdeer.youchat.pojo.Message;
import com.yfuxdeer.youchat.pojo.MessageItem;
import com.yfuxdeer.youchat.utils.Store;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class MessageDaoImpl implements MessageDao {

    JdbcTemplate template = new JdbcTemplate(Store.getDataSource());

    @Override
    public List<Message> findSendToMeAll(String username) {
        return template.query("select distinct `from` from y_message where `to` = ? " +
                        "and `from` in (select friend_id from y_friend where host_id = ?) " +
                        "and `from` <> ?" +
                        "order by `time` desc",
                new BeanPropertyRowMapper<Message>(Message.class), new Object[]{username, username, username});
    }

    @Override
    public List<Message> findISendToAll(String username) {
        return template.query("select distinct `to` from y_message where `from` = ? " +
                        "and `to` in (select friend_id from y_friend where host_id = ?) " +
                        "and `to` <> ?" +
                        "order by `time` desc",
                new BeanPropertyRowMapper<Message>(Message.class), new Object[]{username, username, username});
    }

    @Override
    public MessageItem findLastMessage(String host, String friend) {
        return template.query(
                "select a.friend_id, a.remark, msg as lastMessage, time\n" +
                        "from y_message,\n" +
                        "     y_friend a,\n" +
                        "     y_friend b\n" +
                        "where `from` = a.friend_id\n" +
                        "  and `to` = b.host_id\n" +
                        "  and `to` = ?\n" +
                        "  and `from` = ?\n" +
                        "union\n" +
                        "select friend_id, remark, msg, time\n" +
                        "from y_message,\n" +
                        "     y_friend\n" +
                        "where `from` = host_id\n" +
                        "  and `to` = friend_id\n" +
                        "  and `to` = ?\n" +
                        "  and `from` = ?\n" +
                        "order by time desc\n" +
                        "limit 1", new BeanPropertyRowMapper<MessageItem>(MessageItem.class), new Object[]{
                        host, friend, friend, host
                }).get(0);
    }

    @Override
    public List<Message> findAllMessageWithAFriend(String host, String friend) {
        return template.query("select *\n" +
                        "from y_message\n" +
                        "where (`from` = ? and `to` = ?)\n" +
                        "   or (`to` = ? and `from` = ?)\n" +
                        "order by time",
                new BeanPropertyRowMapper<>(Message.class),
                new Object[]{host, friend, host, friend});
    }
}
